Spreadsheet programs are commonly used for mathematical and database applications such as record-keeping, "what-if" and optimization analysis, engineering and financial calculations, and, recently, charting and graphical analysis. Because of the demands placed on spreadsheet programs by this wide variety of applications, spreadsheet programs have evolved from the relatively simple concept of a single worksheet divided into a grid of cells, into multiple worksheets capable of referencing each other and using complex macros to execute procedures not easily implemented through cell-based functions.
The recent trend toward "component" software and integrated programs, moreover, has spurred the development of spreadsheet programs and "objects" that can communicate with external programs via interprocess communication facilities such as dynamic data exchange protocols and remote procedure calls. Spreadsheet programs capable of such interprocess communication can, for example, export "live" data from a worksheet to a document created in a word processing program. If the data in the worksheet changes, it changes in the document as well. Similarly, a worksheet may import "live" data obtained from an external database through interprocess communication. If the data in the database changes, the change will appear in the worksheet. Generally, these links between the spreadsheet and external programs are one-way: data is either sent to the external program or received from it, but not both.
It is highly desirable to create a two-way link to the external program, so that the external program can receive input data from one part of the spreadsheet, and produce output data, based upon the input data, to another part of the spreadsheet. (This type of external program is termed an "input dependent" external program in this specification.) Integrating an input dependent program with a spreadsheet would enable the spreadsheet to manipulate the inputs of the external program, and simultaneously to chart, display, and use the outputs of the external program.
An excellent example of an input dependent program that could benefit from integration with a spreadsheet is an electronic circuit simulator program. Circuit simulators are used to evaluate numerical models of electronic circuits. Typically, these simulators are designed to read a text file containing a description of the circuit model and values for the circuit's components, such as the resistance of resistors or the capacitance of capacitors. The simulator evaluates the model and produces output data representing, for example, the DC voltage at certain nodes in the model. To change the model, the user must edit the text file and re-run the simulator. This process is cumbersome and is not easily automated. If, instead, the simulator could be integrated with a spreadsheet, the input values of the circuit components could be defined in cells within the spreadsheet, and the desired output information could be retrieved as functions stored in other cells within the spreadsheet. In this way, the user could change the model simply by changing the input values in the spreadsheet. More importantly, spreadsheet functions could be applied to the circuit model so that, for example, (1) algorithms for evaluating the circuit model for a range of input values could be set up within the spreadsheet, automating the simulation process; (2) spreadsheet-based goal-seeking and optimization algorithms could be used to modify the circuit model input values until a desired output is reached; (3) the results of one simulation could be used as the inputs to another simulation; or (4) the spreadsheet could be used to chart and plot the output data from the simulator.
Moreover, it is highly desirable to create a "seamless" two-way link between the spreadsheet and the external program, so that the two appear to be fully integrated. In such a seamless integration, the transfer of data to and from the external program would appear to be completely automatic, occurring in the normal course of the spreadsheet's recalculation. In addition, such a "seamless" two-way link between the spreadsheet and external program would allow the user to place input data for the external program in worksheet cells separate from other worksheet cells where output from the program is desired. For example, the user might wish to place input data for the external program in a range of cells in the upper left corner of the worksheet, perhaps in a table format. The user might also wish to place output data from the external program in one or more cells at the bottom of the worksheet, so that the outputs appear below the table. Seamlessly linking the spreadsheet and external program would allow the user to accomplish this easily, and furthermore would spare the user the unenviable and error-prone task of identifying all of the input data to the external program as arguments of his output functions. In other words, the user would not be required to write output functions such as
GetExternalProgramOutput( WhatOutput, cell1, cell2, cell3, cell4) PA1 GetExternalProgramOutput( WhatOutput)
where WhatOutput specifies the type of output desired, and cell1 . . . cell4 specify the locations of (or data contained in) the cells holding input data for the external program. (Worse still, the user would have to use such verbose functions in every location where he desires output.) Instead, the user might merely write a function such as
to retrieve the program's output. The seamless link between the spreadsheet and program would take care of providing the external program with the proper input data.
In order to create such a seamless integration, it is necessary to ensure that the spreadsheet transfers input data to the external program automatically, whenever that data is changed in the spreadsheet. Equally importantly, it is necessary to ensure that input data is sent before output data is requested from the external program. Otherwise, the external program will execute using old input data, and will likely produce incorrect output data.
In normal operation, spreadsheet programs use an "evaluation sequencer," a function that ensures that the functions in the worksheet operate on the correct data, and that all of the proper cells are reevaluated whenever data in the worksheet changes. The evaluation sequencer determines, in advance of any recalculation, which functions depend upon (i.e., have as arguments) the changed cell or cells in the worksheet, and which functions depend upon those functions, and so on. (Normally, these functions are contained in cells; but in many advanced spreadsheet programs, the functions may be contained in macros or other structures, as well). The spreadsheet program then evaluates the dependent functions in the order of their dependency.
Similarly, were the spreadsheet to use interprocess communication to transmit and retrieve live data, the spreadsheet program's evaluation sequencer would schedule the evaluation of any transmission and retrieval functions within the worksheet according to its normal rules of precedence-that is, the transmission and retrieval functions would be evaluated whenever the cells they depend upon change. It is therefore necessary to ensure that the spreadsheet program recognizes the data dependencies of the retrieval functions in the worksheet, so that they will not be evaluated before the transmission functions are evaluated. In this manner, the user may be assured that the external program operates on the correct input data before providing output to the spreadsheet.
There is thus a need for a seamless two-way link between an input dependent external program and a spreadsheet, such that the transfer of input data to the external program occurs automatically whenever the input data changes within the spreadsheet, and such that the retrieval of output data from the external program occurs after the input data has been transmitted.